/**
 * 
 */
package com.model;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.fileupload.FileItemIterator;
import org.apache.commons.fileupload.FileItemStream;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.javabean.AddDevice;
import com.javabean.CanelBf;
import com.javabean.DeleteDevice;
import com.javabean.User;
import com.model.daemon.MetaDelete;

/**
 * @author cyfex
 * 
 */
public class DeviceService {

	public static Boolean deviceDelete(HttpServletRequest request) {
		try {
			int userId = (int) ((User) request.getSession(true).getAttribute("user"))
					.getUserId();
			String[] dids = request.getParameterValues("delid");
			DeleteDevice test = new DeleteDevice();
			for (int i = 0; i < dids.length; i++) {
				if (test.isdelete(Integer.parseInt(dids[i]))) {
					test.delete_record(Integer.parseInt(dids[i]), userId);
				}
			}
			return true;
		} catch(Exception e) {
			return false;
		}
	}
	public static String deviceExport(HttpServletRequest request) {
		// Connect Database
		try {
			Connection con = DataBase.getConnection();
			/*
			 * Create the excel file, obtaining data from DB
			 */
			String excelPath = request.getSession().getServletContext()
					.getRealPath("downloads");
			File excelDir = new File(excelPath);
			if (!excelDir.exists()) {
				excelDir.mkdir();
			}
			/*
			 * Create the workbook and corresponding sheets of the excel file
			 */
			Workbook wb = new HSSFWorkbook();
			Sheet sheetType = wb.createSheet("设备类别");
			Sheet sheetDevice = wb.createSheet("设备详细");
			Sheet sheetHistory = wb.createSheet("设备出入记录");

			/*
			 * sheetType part, DB to excel, procedure
			 */
			Row rowTypeTitle = sheetType.createRow((short) 0);
			rowTypeTitle.createCell(0).setCellValue("类别");
			rowTypeTitle.createCell(1).setCellValue("数量");
			rowTypeTitle.createCell(2).setCellValue("可用");
			rowTypeTitle.createCell(3).setCellValue("单位");
			PreparedStatement psType = con
					.prepareStatement("SELECT type,amount,available,unit FROM device_type");
			ResultSet rsType = psType.executeQuery();
			String typeUnit;
			int i = 1;
			while (rsType.next()) {
				// add a new line
				Row rowType = sheetType.createRow(i);
				i++;
				// add content of item information
				rowType.createCell(0).setCellValue(rsType.getString(1));
				rowType.createCell(1).setCellValue(rsType.getString(2));
				rowType.createCell(2).setCellValue(rsType.getString(3));
				if ((typeUnit = rsType.getString(4)) != null)
					rowType.createCell(3).setCellValue(typeUnit);
			}
			rsType.close();

			/*
			 * sheetDevice part, DB to excel, procedure
			 */
			Row rowDeviceTitle = sheetDevice.createRow((short) 0);
			rowDeviceTitle.createCell(0).setCellValue("设备类别");
			rowDeviceTitle.createCell(1).setCellValue("设备编号");
			rowDeviceTitle.createCell(2).setCellValue("设备型号");
			rowDeviceTitle.createCell(3).setCellValue("生产厂家");
			rowDeviceTitle.createCell(4).setCellValue("制造日期");
			rowDeviceTitle.createCell(5).setCellValue("截止日期");
			rowDeviceTitle.createCell(6).setCellValue("使用部门");
			rowDeviceTitle.createCell(7).setCellValue("备注");
			rowDeviceTitle.createCell(8).setCellValue("使用人");
			rowDeviceTitle.createCell(9).setCellValue("是否报废");
			rowDeviceTitle.createCell(10).setCellValue("是否删除");
			PreparedStatement psDevice = con
					.prepareStatement("SELECT type,serial,model,vender,mdate,edate,department,remark,username,isUse,state FROM device ORDER BY state DESC, isUse DESC");
			ResultSet rsDevice = psDevice.executeQuery();
			String dvSerial, dvModel, dvVender, dvMdate, dvEdate, dvDepartment, dvRemark, dvUsername;

			int j = 1;
			while (rsDevice.next()) {
				// add a new line
				Row rowDevice = sheetDevice.createRow(j);
				j++;
				// add content of history information
				rowDevice.createCell(0).setCellValue(rsDevice.getString(1));
				if ((dvSerial = rsDevice.getString(2)) != null)
					rowDevice.createCell(1).setCellValue(dvSerial);
				rowDevice.createCell(1).setCellValue(rsDevice.getString(2));
				if ((dvModel = rsDevice.getString(3)) != null)
					rowDevice.createCell(2).setCellValue(dvModel);
				if ((dvVender = rsDevice.getString(4)) != null)
					rowDevice.createCell(3).setCellValue(dvVender);
				if ((dvMdate = rsDevice.getString(5)) != null)
					rowDevice.createCell(4).setCellValue(dvMdate);
				if ((dvEdate = rsDevice.getString(6)) != null)
					rowDevice.createCell(5).setCellValue(dvEdate);
				if ((dvDepartment = rsDevice.getString(7)) != null)
					rowDevice.createCell(6).setCellValue(dvDepartment);
				if ((dvRemark = rsDevice.getString(8)) != null)
					rowDevice.createCell(7).setCellValue(dvRemark);
				if ((dvUsername = rsDevice.getString(9)) != null)
					rowDevice.createCell(8).setCellValue(dvUsername);
				switch (rsDevice.getString(10)) {
				case "yes":
					rowDevice.createCell(9).setCellValue("正常");
					break;
				case "no":
					rowDevice.createCell(9).setCellValue("报废");
					;
					break;
				default:
					break;
				}
				switch (rsDevice.getString(11)) {
				case "yes":
					rowDevice.createCell(10).setCellValue("正常");
					break;
				case "no":
					rowDevice.createCell(10).setCellValue("已删除");
					;
					break;
				default:
					break;
				}
			}
			rsDevice.close();

			/*
			 * sheetHistory part, DB to excel, procedure
			 */
			Row rowHistoryTitle = sheetHistory.createRow((short) 0);
			rowHistoryTitle.createCell(0).setCellValue("设备类型");
			rowHistoryTitle.createCell(1).setCellValue("设备编号");
			rowHistoryTitle.createCell(2).setCellValue("时间");
			rowHistoryTitle.createCell(3).setCellValue("操作者");
			rowHistoryTitle.createCell(4).setCellValue("出入库情况");
			rowHistoryTitle.createCell(5).setCellValue("位置");
			PreparedStatement psHistory = con
					.prepareStatement("SELECT type,serial,BorrowDate,user.UserName,BorrowOrReturn,Position FROM borrow_record left join device on device.did=borrow_record.DeviceId left join user on borrow_record.BorrowerId=user.UserId");
			ResultSet rsHistory = psHistory.executeQuery();
			String hsSrl, hsDate, hsUser, hsPos;
			int k = 1;
			while (rsHistory.next()) {
				// add a new line
				Row rowHistory = sheetHistory.createRow(k);
				k++;
				// add content of item information
				rowHistory.createCell(0).setCellValue(rsHistory.getString(1));
				if ((hsSrl = rsHistory.getString(2)) != null)
					rowHistory.createCell(1).setCellValue(hsSrl);
				if ((hsDate = rsHistory.getString(3)) != null)
					rowHistory.createCell(2).setCellValue(hsDate);
				if ((hsUser = rsHistory.getString(4)) != null)
					rowHistory.createCell(3).setCellValue(hsUser);
				rowHistory.createCell(4).setCellValue(rsHistory.getString(5));
				if ((hsPos = rsHistory.getString(6)) != null)
					rowHistory.createCell(5).setCellValue(hsPos);
			}
			rsHistory.close();

			/*
			 * Final work, read the excel file from memory to disk, and then
			 * generate the link where the excel file lies, the format of which
			 * is "downloads/device-export-MILLISECONDS"
			 */
			// Absolute path
			excelPath = request
					.getSession()
					.getServletContext()
					.getRealPath(
							"downloads" + File.separatorChar + "device-export-"
									+ System.currentTimeMillis())
					+ ".xls";
			FileOutputStream fileOut = new FileOutputStream(excelPath);
			wb.write(fileOut);
			fileOut.close();

			// Start the singleton daemon thread.
			MetaDelete.getInSingleton(request);

			// Context path
			excelPath = excelPath.substring(request.getSession()
					.getServletContext().getRealPath("/").length());
			excelPath = excelPath.replaceAll("\\\\", "/");

			// Return the context path
			return excelPath;
		} catch (Exception e) {
			e.printStackTrace();
			MetaDelete.getInSingleton(request);
			return null;
		}
	}
	
	public static boolean deviceImport(HttpServletRequest request)
			throws IOException {
		/*
		 * UploadFile
		 */
		// Check that we have a file upload request
		boolean isMultipart = ServletFileUpload.isMultipartContent(request);
		if (!isMultipart) {
			return false;
		}
		// Create a new file uploading handler
		ServletFileUpload upload = new ServletFileUpload();
		// Parse the request
		FileItemIterator iter;
		String uploadedPath = null;
		try {
			iter = upload.getItemIterator(request);
			while (iter.hasNext()) {
				FileItemStream item = iter.next();
				InputStream stream = item.openStream();
				if (item.isFormField()) {
					;
				} else {
					uploadedPath = request.getSession().getServletContext()
							.getRealPath("uploaded");
					File uploadedExcel = new File(uploadedPath);
					if (!uploadedExcel.exists()) {
						uploadedExcel.mkdir();
					}
					// Upload file
					uploadedPath = request
							.getSession()
							.getServletContext()
							.getRealPath(
									"uploaded" + File.separatorChar
											+ System.currentTimeMillis());
					OutputStream os = new FileOutputStream(uploadedPath);
					byte buf[] = new byte[1024];
					int length = 0;
					while ((length = stream.read(buf)) > 0) {
						os.write(buf, 0, length);
					}
					// Close work
					os.flush();
					os.close();
					stream.close();
				}
			}
		} catch (FileUploadException e) {
			e.printStackTrace();
			return false;
		}

		/*
		 * Insert from excel into database
		 */
		AddDevice test = new AddDevice();
		String usr = ((User) request.getSession(true).getAttribute(
				"user")).getUserName();
		int uid = (int) ((User) request.getSession(true).getAttribute(
				"user")).getUserId();
		HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(uploadedPath));
		for (int k = 0; k < wb.getNumberOfSheets(); k++) {
			HSSFSheet sheet = wb.getSheetAt(k);
			int rows = sheet.getPhysicalNumberOfRows();
			for (int r = 0; r < rows; r++) {
				HSSFRow row = sheet.getRow(r);
				if (row == null) {
					continue;
				}
				// 类型 编号 型号 厂家 制造日期 截止日期 部门 备注
				HSSFCell cellTyp = row.getCell(0);
				HSSFCell cellSrl = row.getCell(1);
				HSSFCell cellMdl = row.getCell(2);
				HSSFCell cellVdr = row.getCell(3);
				HSSFCell cellMdt = row.getCell(4);
				HSSFCell cellEdt = row.getCell(5);
				HSSFCell cellDpt = row.getCell(6);
				HSSFCell cellRmk = row.getCell(7);

				String typ = cellTyp.getStringCellValue();
				int srl = (int) cellSrl.getNumericCellValue();
				String mdl = cellMdl.getStringCellValue();
				String vdr = cellVdr.getStringCellValue();
				Date mdt = cellMdt.getDateCellValue();
				Date edt = cellEdt.getDateCellValue();
				String dpt = cellDpt.getStringCellValue();
				String rmk = cellRmk.getStringCellValue();

				test.AddType(typ, 1, 1, "台");
				if (test.AddNew(typ, srl, mdl, dpt, vdr, edt, mdt, rmk, usr))
					test.addRecord(uid, dpt);
				else
					test.AddType(typ, -1, -1, "台");
			}
		}

		/*
		 * Delete uploaded file
		 */
		File uploadedExcel = new File(uploadedPath);
		if (uploadedExcel.exists()
				&& !uploadedExcel.equals(request.getSession()
						.getServletContext().getRealPath("uploaded")))
			uploadedExcel.delete();

		/*
		 * Return error set
		 */

		return true;

	}
	
	public static Boolean deviceScrap(HttpServletRequest request) {
		try {
			String[] dids = request.getParameterValues("delid");
			Connection con = DataBase.getConnection();
			String sql = "update device set isUse=? where did=? ";
			PreparedStatement pre = con.prepareStatement(sql);
			for (int i = 0; i < dids.length; i++) {
				pre.setString(1, "no");
				pre.setInt(2, Integer.parseInt(dids[i]));
				pre.executeUpdate();
			}
			return true;
		} catch(Exception e) {
			return false;
		}
	}
	
	public static Boolean deviceScrapCancel(HttpServletRequest request) {
		try {
			String[] dids = request.getParameterValues("delid");
			CanelBf test = new CanelBf();
			for (int i = 0; i < dids.length; i++) {
				test.canel(Integer.parseInt(dids[i]));

			}
			return true;
		} catch(Exception e) {
			return false;
		}
	}
	
	public static Boolean deviceTranslate(HttpServletRequest request) {
		try {
			String area=request.getParameter("devdepart");
			int did = Integer.parseInt(request.getParameter("did"));
			Connection con = DataBase.getConnection();
			String sql="update device set department=? where did=?";
			PreparedStatement pre=con.prepareStatement(sql);
			pre.setString(1, area);
			pre.setInt(2, did);
			pre.executeUpdate();
			return true;
		} catch(Exception e) {
			return false;
		}
	}

}
